package homework.jdbc.dao;

import homework.jdbc.Utils.JDBCUtils;
import homework.jdbc.domain.User;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Administrator on 2017/9/13.
 */
public class UserDao {
    public List<User> findAll() throws SQLException {
        QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
        String sql = "select * from user";
        //使用封装策略自动封装
        //结果集的封装策略，ResultSetHandle类
        //因为结果有不同的各类,可能是一个集合,可能是一个对象
        //所以使用ResultSetHeandler类的子类,封装集合的是ResultSetHeandler,而封闭的一个对象的BeanHandler子类
        //User.class是封装类型的参考模型,javaBean
        List list = (List) queryRunner.query(sql,new BeanListHandler(User.class));
        return list;
    }

    public User findById(int id) throws SQLException {
        QueryRunner runner = new QueryRunner(JDBCUtils.getDataSource());
        String sql = "select * from user where id = ?";
        User user = (User) runner.query(sql,new BeanHandler(User.class),id);
        return user;
    }

    public List<User> findByPage(int page) throws SQLException {
        final int PAGE_SIZE=3;
        QueryRunner runner = new QueryRunner(JDBCUtils.getDataSource());
        String sql = "select * from user order by id limit ?,?";
        List<User> list = (List) runner.query(sql,new BeanListHandler(User.class),(page-1)*PAGE_SIZE,PAGE_SIZE);
        return list;
    }

    public boolean insert(User user) throws SQLException {
        QueryRunner runner = new QueryRunner(JDBCUtils.getDataSource());
        String sql = "insert into user values(?,?,?,?,?)";
        Connection coon = runner.getDataSource().getConnection();
        try {
            int x = runner.update(sql,user.getId(),user.getUsername(),user.getPassword(),user.getEmail(),user.getPhone());
            if(x==0){
                return false;
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
        return true;
    }

    public boolean delete(int id){
        QueryRunner runner = new QueryRunner(JDBCUtils.getDataSource());
        String sql = "delete from user where id = ?";
        try {
            runner.update(sql,id);
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    public boolean update(User user){
        QueryRunner runner = new QueryRunner(JDBCUtils.getDataSource());
        String sql = "update user set username=?,password=?,email=?,phone=? where id=?";
        try {
            runner.update(sql,user.getUsername(),user.getPassword(),user.getEmail(),user.getPhone(),user.getId());
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }


//    public List<User> findAll(){
//        Connection conn = JDBCUtils.getConnection();
//        PreparedStatement ps=null;
//        ResultSet set=null;
//        ArrayList<User> list = new ArrayList<User>();
//        String sql = "select * from user";
//        try {
//            ps = conn.prepareStatement(sql);
//            set = ps.executeQuery(sql);
//            while (set.next()){
//                User user = new User();
//                user.setId(set.getInt("id"));
//                user.setUsername(set.getString("username"));
//                user.setPassword(set.getString("password"));
//                user.setEmail(set.getString("email"));
//                user.setPhone(set.getString("phone"));
//                list.add(user);
//            }
//            return list;
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
//        return null;
//    }
//
//    public boolean insert(User  user){
//        Connection conn = JDBCUtils.getConnection();
//        PreparedStatement ps=null;
//        String sql = "INSERT INTO USER(id,username,password,email,phone) VALUES (?,?,?,?,?)";
//        try {
//            ps = conn.prepareStatement(sql);
//            ps.setInt(1,user.getId());
//            ps.setString(2,user.getUsername());
//            ps.setString(3,user.getPassword());
//            ps.setString(4,user.getEmail());
//            ps.setString(5,user.getPhone());
//            ps.executeUpdate();
//            return true;
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
//        return false;
//    }
//    public boolean delete(int id){
//        Connection conn = JDBCUtils.getConnection();
//        PreparedStatement ps=null;
//        String sql = "DELETE FROM USER WHERE id = ?";
//        try {
//            ps = conn.prepareStatement(sql);
//            ps.setInt(1,id);
//            ps.executeUpdate();
//            return true;
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
//        return false;
//    }
//    public boolean update(User user){
//        Connection conn = JDBCUtils.getConnection();
//        PreparedStatement ps=null;
//        String sql = "update user set id=?,username=?,password=?,email=?,phone=? where id=?,username=?,password=?,email=?,phone=?";
//        try {
//            ps = conn.prepareStatement(sql);
//            if(user.getId()!=0){
//
//            }
//            ps.executeUpdate(sql);
//            return true;
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
//        return false;
//    }
//    public User findById(int id){
//        Connection conn = JDBCUtils.getConnection();
//        PreparedStatement ps=null;
//        ResultSet set=null;
//        String sql = "select * from user where id = ?";
//        User user = new User();
//        try {
//            ps = conn.prepareStatement(sql);
//            ps.setInt(1,id);
//            set=ps.executeQuery();
//            while (set.next()){
//                user.setId(set.getInt("id"));
//                user.setUsername(set.getString("username"));
//                user.setPassword(set.getString("password"));
//                user.setEmail(set.getString("email"));
//                user.setPhone(set.getString("phone"));
//            }
//            return user;
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
//        return null;
//    }
//    public List<User> findByPage(int page){
//        final int PAGESIZE = 3;
//        Connection conn = JDBCUtils.getConnection();
//        String sql = "select * from user order by id asc limit ?,?";
//        ArrayList<User> list = new ArrayList();
//        try {
//            PreparedStatement ps  = conn.prepareStatement(sql);
//            ps.setInt(1,(page-1)*PAGESIZE);
//            ps.setInt(2,PAGESIZE);
//            ps.executeQuery();
//            ResultSet set = ps.getResultSet();
//            while (set.next()){
//                User user = new User();
//                user.setId(set.getInt("id"));
//                user.setUsername(set.getString("username"));
//                user.setPassword(set.getString("password"));
//                user.setEmail(set.getString("email"));
//                user.setPhone(set.getString("phone"));
//                list.add(user);
//            }
//            return list;
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
//        return null;
//    }
}
